package com.sl.au.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Bid;
import com.sl.au.entity.DeviceDetail;
import com.sl.au.entity.MaintainItem;
import com.sl.au.entity.MaintainItemDevice;
import com.sl.au.entity.MaintainScheme;
import com.sl.au.entity.QuoteDevice;

/**
 * 
 * @author XH
 *
 */
@Transactional(readOnly = true)
public interface MaintainSchemeRepository extends JpaRepository<MaintainScheme, String> {

	/**
	 * 通过条件得到维护方案
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select ms from MaintainScheme ms where ms.deviceID=?1 "
			+ "order by ms.deviceID desc")
	Page<MaintainScheme> findMaintainScheme(String key, Pageable pageable);

	/**
	 * 通过条件得到维护方案条数
	 * @param key
	 * @return
	 */
	@Query(" select count(ms) from MaintainScheme ms where ms.deviceID=?1 ")
	Long getCount(String key);
	
	/**
	 * 得到当前维护方案的维护项目
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select mi from MaintainItem mi where mi.maintainSchemeID=?1 ")	
	Page<MaintainItem> findMaintainItem(String key, Pageable pageable);
	
	/**
	 * 得到当前维护方案的维护项目条数
	 * @param key
	 * @return
	 */
	@Query(" select count(mi) from MaintainItem mi where mi.maintainSchemeID=?1 ")	
	Long getItemCount(String key);

	/**
	 * 删除与维护方案相关的维护项目
	 * @param schemeId
	 * @return
	 */
	@Transactional
	@Modifying  
	@Query("delete from MaintainItem mi where mi.maintainSchemeID=?1 ")
	void deleteItemBySchemeId(String schemeId);

	/**
	 * 得到当前维护方案的所有维护项目
	 * @param maintainSchemeID
	 * @param pageable
	 * @return
	 */
	@Query(" select mi from MaintainItem mi where mi.maintainSchemeID=?1 order by createTime asc ")	
	List<MaintainItem> getAllMaintainItemBySchemeID(String schemeID);

	/**
	 * 得到当前设备的维护项目
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.deviceID=?1 ")	
	Page<MaintainItemDevice> findMaintainItemDevice(String key, Pageable pageable);
	
	/**
	 * 得到当前设备的维护项目List
	 * @param deviceID
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.deviceID=?1 order by createTime asc ")	
	List<MaintainItemDevice> getMaintainItemDevice(String deviceID);
	
	/**
	 * 得到当前设备的维护项目开始时间晚于被编辑维护项目的List
	 * @param deviceID
	 * @param startDate
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.deviceID=?1 and mid.startDate>?1 order by createTime asc ")	
	List<MaintainItemDevice> getMaintainItemDeviceByStartDate(String deviceID, Date startDate);
	
	/**
	 * 得到当前设备的维护项目条数
	 * @param key
	 * @return
	 */
	@Query(" select count(mid) from MaintainItemDevice mid where mid.deviceID=?1 ")	
	Long getDeviceItemCount(String key);

	/**
	 * 删除与设备相关的维护项目
	 * @param deviceID
	 * @return
	 */
	@Transactional
	@Modifying  
	@Query("delete from MaintainItemDevice mid where mid.deviceID=?1 ")
	void deleteItemByDeviceId(String deviceID);
	/**
	 * 得到所有有维护项目的维护方案
	 * @param pageable
	 * @return
	 */
	@Query(" select ms from MaintainScheme ms where ms.id in (select mi.maintainSchemeID from MaintainItem mi group by mi.maintainSchemeID)")
	Page<MaintainScheme> findAllSchemes(Pageable pageable);
	/**
	 * 得到所有有维护项目的维护方案条数
	 * @return
	 */
	@Query(" select count(*) as num from MaintainScheme ms where ms.id in (select mi.maintainSchemeID from MaintainItem mi group by mi.maintainSchemeID)")
	Long getAllSchemesCount();
	/**
	 * 通过条件得到有维护项目的维护方案
	 * @param key1
	 * @param pageable
	 * @return
	 */
	@Query(" select ms from MaintainScheme ms where ( ms.schemeName like '%'+?1+'%' or ms.schemeCode "
			+ " like '%'+?1+'%' or ms.type like '%'+?1+'%' or ms.deviceType like '%'+?1+'%' ) "
			+ " and ms.id in (select mi.maintainSchemeID from MaintainItem mi group by mi.maintainSchemeID)")
	Page<MaintainScheme> findSchemes(String key1, Pageable pageable);
	/**
	 * 通过条件得到有维护项目的维护方案条数
	 * @param key1
	 * @return
	 */
	@Query(" select count(*) as num from MaintainScheme ms where ( ms.schemeName like '%'+?1+'%' or ms.schemeCode "
			+ " like '%'+?1+'%' or ms.type like '%'+?1+'%' or ms.deviceType like '%'+?1+'%' ) "
			+ " and ms.id in (select mi.maintainSchemeID from MaintainItem mi group by mi.maintainSchemeID)")
	Long getSchemesCount(String key1);
	/**
	 * 得到当前合同的维护项目条数
	 * @param contractID
	 * @return
	 */
	@Query(" select count(mid) from MaintainItemDevice mid where mid.contractID=?1 ")
	Long getPlansCount(String contractID);
	/**
	 * 得到当前合同的维护项目
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select mid from MaintainItemDevice mid where mid.contractID=?1 ")
	Page<MaintainItemDevice> findMaintainPlans(String key, Pageable pageable);

	/**
	 * 得到当前合同的泵数据
	 * @param contractID
	 * @return
	 */
	@Query(" select dd from DeviceDetail dd where dd.contractID=?1 order by dd.createTime desc ")
	Page<DeviceDetail> getAllDetailById(String contractID, Pageable pageable);

	/**
	 * 得到当前合同的泵数据条数
	 * @param contractID
	 * @return
	 */
	@Query(" select count(dd) from DeviceDetail dd where dd.contractID=?1 ")
	long getAllDetailCountById(String contractId);
	
	/**
	 * 得到当前合同的有出厂信息的泵数据
	 * @param contractID
	 * @return
	 */
	@Query(" select dd from DeviceDetail dd where dd.contractID=?1 and dd.leaveFactoryCode is not null "
			+ " and dd.leaveFactoryCode != '' and dd.leaveFactoryDate is not null and dd.leaveFactoryDate != '' "
			+ " order by dd.createTime desc ")
	Page<DeviceDetail> getAllPlanDetailById(String contractID, Pageable pageable);

	/**
	 * 得到当前合同的有出厂信息的泵数据条数
	 * @param contractID
	 * @return
	 */
	@Query(" select count(dd) from DeviceDetail dd where dd.contractID=?1 and dd.leaveFactoryCode is not null "
			+ " and dd.leaveFactoryCode != '' and dd.leaveFactoryDate is not null and dd.leaveFactoryDate != '' ")
	long getAllPlanDetailCountById(String contractId);

	/**
	 * 得到当前合同的泵数据(带数量的)
	 * @param contractID
	 * @return
	 */
	@Query("select qd from QuoteDevice qd where qd.contractID=?1 order by qd.createTime desc ")
	List<QuoteDevice> getDeviceById(String contractID);

	/**
	 * 获得当前设备数量拆分后的条数
	 */
	@Query(" select count(dd) from DeviceDetail dd where dd.deviceID=?1 ")
	long getDeviceDetailCountById(String deviceId);

	/**
	 * 通过方案编号查询方案
	 */
	@Query(" select ms from MaintainScheme ms where ms.schemeCode=?1 ")
	List<MaintainScheme> getSchemeByCode(String code);

	/**
	 * 通过设备出厂编号查询设备
	 */
	@Query(" select dd from DeviceDetail dd where dd.leaveFactoryCode=?1 ")
	List<DeviceDetail> getDeviceByCode(String leaveFactoryCode);
}
